/****** Object:  Stored Procedure dbo.asi_PurgeInactiveSegmentationJobs    Script Date: 7/3/2003 4:58:05 PM ******/
CREATE PROCEDURE [dbo].[asi_PurgeInactiveSegmentationJobs]

AS

DECLARE @jobKey uniqueidentifier
DECLARE @defKey uniqueIdentifier

SET ROWCOUNT  1

SELECT @jobKey = SegmentationJobKey FROM vBoSegmentationJob WHERE SegmentationJobStatusCode=1
while @@ROWCOUNT > 0
begin

    SET ROWCOUNT 1

    --Create a loop to delete all Segmentation Definitions associated with the selected Segmentation Job
    SELECT @defKey = SegmentDefinitionKey FROM vBoSegmentationDef WHERE SegmentationJobKey = @jobKey
    while @@ROWCOUNT > 0
    begin
        --Delete the rows that comprise the selected Segmentation Definition
        SET ROWCOUNT 0
        DELETE FROM ListItem WHERE ListKey = @defKey        
        DELETE FROM ListMain WHERE ListKey = @defKey
        DELETE FROM SegmentDefinition WHERE SegmentDefinitionKey = @defKey
        DELETE FROM UniformRegistry WHERE UniformKey = @defKey

        --Attempt to select the next Segmentation Definition for deletion
        SELECT @defKey = SegmentDefinitionKey FROM vBoSegmentationDef WHERE SegmentationJobKey = @jobKey
    end

    --Delete the rows that comprise the selected Segmentation Job
    DELETE FROM SegmentationJob WHERE SegmentationJobKey = @jobKey
    DELETE FROM UniformRegistry WHERE UniformKey = @jobKey

    --Attempt to select the next Segmentation Job for deletion        
    SELECT @jobKey = SegmentationJobKey FROM vBoSegmentationJob WHERE SegmentationJobStatusCode=1

end

GO